﻿///////////////////////////////////////////////////////////////
//                                                           //
// Code from Arcanea Project.                                //
//                                                           //
// Please include this license part to every reproduction    //
//   of code.                                                //
//                                                           //
// This code is under Ms-RL license                          //
//                                                           //
// Please read futher information about the license,         //
//   the project (news and updates) and the                  //
//   contributors on the address :                           //
//     http://www.codeplex.com/Arcanea/                      //
//                                                           //
///////////////////////////////////////////////////////////////
using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using Arcanea.Mediator.Toolbox;
using Arcanea.Mediator.XmlClasses;
using System.Data.SqlClient;
using Arcanea.BaseEngine.Interfaces;
using Arcanea.BaseEngine.SqlServer;
using System.Reflection;
using log4net;
using Arcanea.BaseEngine.Generics;
using Arcanea.Mediator;
using System.Globalization;

namespace Arcanea.BaseEngine.Generics
{
    /// <summary>
    /// Classe qui assure la coherence de la base.
    /// Ex : la base existe elle ? ou les colonnes sont elles crees ?
    /// </summary>
    /// <typeparam name="BaseAccessType"></typeparam>
    public class CoherenceEngine<TBaseAccessType> where TBaseAccessType : IBaseAccess, new()
    {
        protected static readonly ILog logger = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        PoolSwitcher<TBaseAccessType> pool = null;

        /// <summary>
        /// Base qui doit etre Ajoutee dans le SGBD
        /// </summary>
        public readonly string databaseName = "Arcanea";

        /// <summary>
        /// Initialise le module de coherence.
        /// </summary>
        /// <param name="connectionString">String de connection</param>
        public void Initialise(string connectionString)
        {
            pool = new PoolSwitcher<TBaseAccessType>();
            pool.Initialise(connectionString, 10);
        }

        /// <summary>
        /// Lance le module de coherence de la base.
        /// Pour chaque paquet :
        /// Verifie si la table existe sinon elle est cree
        /// Verifie si chaque colone est cree, sinon on la cree
        /// Verifie si il y a trop de colone : aucune action, consulter les logs
        /// </summary>
        public void SetCoherence()
        {
            logger.Info("Start of setting coherence");
            IList<XmlPacket> allPackets = FullToolbox.Instance.allPackets;

            foreach (XmlPacket p in allPackets)
            {
                if (p.ChargerEnBase)
                {
                    //On vérifie que la table existe
                    if (tableExist(p.Name))
                    {
                        List<string> allColumns = getAllColumns(p.Name);
                        foreach (XmlField f in p.Fields)
                        {
                            if (!allColumns.Contains(f.Name.ToLower(CultureInfo.CurrentCulture)))
                            {
                                addColumn(p.Name.ToLower(CultureInfo.CurrentCulture), f);
                            }
                            allColumns.Remove(f.Name.ToLower(CultureInfo.CurrentCulture));
                        }

                        //On supprime les colonnes en trop. Attention possibilité de perdre des données... On le laisse au soin de l'utilisateur.
                        foreach (string s in allColumns)
                        {
                            logger.Fatal("User have to delete column " + s + " in base " + p.Name);
                            //deleteColumn(p.Name, s);
                        }

                    }
                    //On crée la table
                    else
                    {
                        logger.Info("Creating table " + p.Name);
                        createTable(p);
                    }
                }
            }
            logger.Info("End of setting coherence");
        }

        #region Private Sql engine for database gestion

        private bool tableExist(string name)
        {
            String requete = "SHOW TABLES FROM " + databaseName;

            IBaseAccess con = pool.GetConnection();

            IBaseResultSet rs = con.ExecuteQuery(requete);

            while (rs.NextResult())
            {
                if (name == rs.GetString(0))
                    return true;
            }

            rs.Close();

            pool.FreeConnection(con);

            return false;
        }

        private bool createTable(XmlPacket packet)
        {
            String requete = "CREATE TABLE ";
            String PrimaryKey = "";
            requete += "`" + packet.Name + "`";
            requete += " (";

            string SqlType = null;

            foreach (XmlField f in packet.Fields)
            {
                if (f.IsIDPK)
                    PrimaryKey = "`" + f.Name + "`";

                SqlType = TypeDatabase.GetSqlType(f.SystemType);

                requete += "`" + f.Name + "` " + SqlType + (f.NotNull ? " NOT NULL " : "") + (f.IsIDPK ? " AUTO_INCREMENT " : "") + " ,";
            }
            if (PrimaryKey.Length != 0)
                requete += "PRIMARY KEY ( " + PrimaryKey + " )";
            else if (packet.Fields != null && packet.Fields.Count != 0)
                requete = requete.Substring(0, requete.Length - 1);

            requete += ") COMMENT = '" + packet.Description + "'";

            IBaseAccess con = pool.GetConnection();
            con.ExecuteSimple(requete);
            pool.FreeConnection(con);

            return true;
        }

        private bool addColumn(string tableName, XmlField f)
        {
            string SqlType = TypeDatabase.GetSqlType(f.SystemType);
            string requete = "ALTER TABLE `" + tableName + "` ADD `" + f.Name + "` " + SqlType + " " + (f.NotNull ? "NOT NULL" : "");
            IBaseAccess con = pool.GetConnection();
            con.ExecuteSimple(requete);
            pool.FreeConnection(con);
            return true;
        }

        private bool deleteColumn(string tableName, string columnName)
        {
            string requete = "ALTER TABLE `" + tableName + "` DROP `" + columnName + "` ";
            IBaseAccess con = pool.GetConnection();
            con.ExecuteSimple(requete);
            pool.FreeConnection(con);
            return true;
        }

        private List<string> getAllColumns(string tableName)
        {
            string requete = "SHOW COLUMNS FROM " + tableName ;
            IBaseAccess con = pool.GetConnection();
            IBaseResultSet rs = con.ExecuteQuery(requete);

            List<string> result = new List<string>();

            while (rs.NextResult())
            {
                result.Add(rs.GetString(0).ToLower(CultureInfo.CurrentCulture));
            }
            rs.Close();
            pool.FreeConnection(con);
            return result;
        }

        #endregion

    }
}
